# -*- coding: utf-8 -*-
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

#读取数据
def read_excel_data(file_path):
    return pd.read_excel(file_path, sheet_name='Bug')

# 统计所属模块的bug数量
def count_bug_by_module(df):
    module_bug_counts = df['所属模块'].value_counts().reset_index()
    module_bug_counts.columns = ['所属模块', 'bug数量']
    return module_bug_counts

# 统计不同优先级的bug数量
def count_bug_by_priority(df):
    priority_1_counts = df[df['优先级'] == 1].groupby('所属模块').size().reset_index(name='优先级1')
    priority_2_counts = df[df['优先级'] == 2].groupby('所属模块').size().reset_index(name='优先级2')
    priority_3_counts = df[df['优先级'] == 3].groupby('所属模块').size().reset_index(name='优先级3')
    priority_4_counts = df[df['优先级'] == 4].groupby('所属模块').size().reset_index(name='优先级4')
    priority_5_counts = df[df['优先级'] == 5].groupby('所属模块').size().reset_index(name='优先级5')
    priority_nan_counts = df[df['优先级'].isnull()].groupby('所属模块').size().reset_index(name='未评级')
    return [priority_1_counts, priority_2_counts, priority_3_counts, priority_4_counts, priority_5_counts,
            priority_nan_counts]

# 汇总结果
def merge_result(module_bug_counts, priority_counts_list):
    merged_result = module_bug_counts
    for priority_counts in priority_counts_list:
        merged_result = pd.merge(merged_result, priority_counts, on='所属模块', how='left')
    merged_result = merged_result.fillna(0).astype({'优先级1': np.int16, '优先级2': np.int16, '优先级3': np.int16, '优先级4': np.int16, '优先级5': np.int16,
                                                    '未评级': np.int16})
    return merged_result

# 保存结果
def write_excel(input_file_path, output_file_path, merged_result):
    # 加载原始文件数据
    wb = load_workbook(input_file_path)
    ws = wb.create_sheet('统计结果', 0)
    for r in dataframe_to_rows(merged_result, index=False):
        ws.append(r)
    # 设置字体大小
    font = Font(size=11)
    for row in ws.iter_rows():
        for cell in row:
            cell.font = font
    for column in ws.columns:
        max_length = 0
        column_letter = get_column_letter(column[0].column)
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except Exception as e:
                pass
        adjusted_width = (max_length + 11)
        ws.column_dimensions[column_letter].width = adjusted_width
    wb.save(output_file_path)


# 设置 matplotlib 支持中文
plt.rcParams['font.family'] = 'SimHei'  # 使用黑体字体，可根据系统情况替换为其他支持中文的字体
# 解决负号显示问题
plt.rcParams['axes.unicode_minus'] = False

# 绘制所属模块 bug 数前十五的图
def plot_top_ten_bugs(module_bug_counts):
    top_ten = module_bug_counts.nlargest(15, 'bug数量')
    plt.figure(figsize=(10, 6))
    bars = plt.bar(top_ten['所属模块'], top_ten['bug数量'])
    plt.xlabel('所属模块')
    plt.ylabel('bug 数量')
    plt.title('所属模块 bug 数前十五')
    plt.xticks(rotation=45)
    # 添加数据标签
    for bar in bars:
        height = bar.get_height()
        plt.annotate(f'{height}',
                     xy=(bar.get_x() + bar.get_width() / 2, height),
                     xytext=(0, 3),
                     textcoords="offset points",
                     ha='center', va='bottom')
    plt.tight_layout()
    plt.show()


def count_bug_data(input_file_path, output_file_path):
    df = read_excel_data(input_file_path)
    df = df[(df['解决方案'].isnull()) | (df['解决方案'] == '已解决')]
    module_bug_counts = count_bug_by_module(df)
    priority_counts_list = count_bug_by_priority(df)
    merged_result = merge_result(module_bug_counts, priority_counts_list)
    write_excel(input_file_path, output_file_path, merged_result)
    plot_top_ten_bugs(module_bug_counts)

input_file = '【iSA】iCAS_术前规划系统-Bug (2).xlsx'
output_file = 'bug统计结果.xlsx'
count_bug_data(input_file, output_file)

